import time

from chdwlgcsys.settings import BASE_DIR
from chdwlgcsys.src.sqlControlFYB import sqlSelectData, sqlUpdateDataLine, sqlInsertDataLine, sqlInsertDataMultLine, \
    sqlDeleteData, colNameMatrxiToString, stringToColNameMatrix
from django.core.serializers.json import json
from django.http import HttpResponse
from chdwlgcsys.src.sysMain import dbTableHead

# 配置数据库
dataBaseSql = {
    'default': {
        'ENGINE': 'django.db.backends.sqlite3',  # 数据库引擎
        'TYPE': 'sqlite3',  # 数据库类型
        'NAME': BASE_DIR / 'db.sqlite3',  # 数据库名称
        'USER': 'root',  # 数据库用户名,这个可以不要
        'PASSWORD': 'dragon',  # 数据库密码,这个可以不要
        'CHARSET': 'UTF8',  # 字节集 UTF8
    },
    'mysql': {
        'ENGINE': 'django.db.backends.mysql',  # 数据库引擎
        'TYPE': 'mysql',  # 数据库类型
        'NAME': 'chdwlgcsys',  # 数据库名称
        'HOST': 'localhost',  # 数据库地址，本机 ip 地址 127.0.0.1
        'PORT': 3306,  # 端口
        'USER': 'root',  # 数据库用户名
        'PASSWORD': 'dragon',  # 数据库密码
        'CHARSET': 'UTF8',  # 字节集 UTF8
    }
}

# 配置例外表格,这些表格不能通过dataTransferFyb进行操作，目的是为了保证安全
exceptTable = {
    'default': [dbTableHead + 'user_infor']
}


def dataTransferFyb(request):
    if request.method == "POST":  # ‘POST’方法传递数据
        sqlType = request.POST.get("sqlType")
        userAccount = request.POST.get("userAccount")  # 用户唯一账号
        enterAccount = request.POST.get("enterAccount")  # 企业唯一账号
        tableName = request.POST.get("table")  # 操作表格的名称
        queTableBool = exceptTableJudge('default', tableName)
        #print(userAccount, enterAccount)
        if userAccount != "" and enterAccount != "" and queTableBool == False:  # 用户ID和企业ID不能为空，后期验证安全性，可以在这里添加
            if sqlType == "query":  # 查询数据库
                column = request.POST.get("column")
                columnMatrix = stringToColNameMatrix(column)  # 将’name, phoneid, shunxu‘格式转化为List['name', 'phoneid', 'shunxu']
                restraint = request.POST.get("restraint")
                testDataTemp = []  # 新建矩阵，用来保存返回的数据
                restraintFull = "data_owner_user_account='" + userAccount + "' and enter_account='" + enterAccount + "'"
                if restraint != "":
                    restraintFull = restraintFull + " and " + restraint
                sqlFetchData = sqlSelectData(tableName, columnMatrix, restraintFull, dataBaseSql['default'])
                #print(sqlFetchData)
                for i in range(len(sqlFetchData)):  # 遍历取回的数据
                    sqlDataDict = {}  # 新建字典
                    for j in range(len(columnMatrix)):  # 按照列名称，将数据保存到字典中
                        sqlDataDict[columnMatrix[j]] = sqlFetchData[i][j]
                    testDataTemp.append(sqlDataDict)
                if len(sqlFetchData) == 0:  # 如果是空数据
                    sqlDataDict = {}  # 新建字典
                    for j in range(len(columnMatrix)):  # 按照列名称，将数据保存到字典中
                        sqlDataDict[columnMatrix[j]] = ''
                    testDataTemp.append(sqlDataDict)
                # sqlDataDict.clear()#清空字典
                # testDataTemp=[{"name": 'abc',"result": 'testBack',},{"name": 'abcdd',"result": 'rtg',}]
                bakString = json.dumps(testDataTemp)
            elif sqlType == "insert":  # 保存数据到数据库
                columnName = request.POST.get("column")
                columnName = columnName + ", data_owner_user_account, creat_user_account, enter_account, creat_time"
                columnNameMatrix = stringToColNameMatrix(columnName)  # 把列的字符串转化为矩阵
                dataInsert = request.POST.get("saveData")
                creatTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 生成数据的创建时间
                print(creatTime)
                dataInsertMatrix = stringToColNameMatrix(dataInsert)
                dataInsertMatrix.append(userAccount)
                dataInsertMatrix.append(userAccount)
                dataInsertMatrix.append(enterAccount)
                dataInsertMatrix.append(creatTime)
                sqlInsertData = sqlInsertDataLine(tableName, columnNameMatrix, dataInsertMatrix, dataBaseSql['default'])
                bakString = json.dumps([{"insert": sqlInsertData}])
            elif sqlType == "delete":  # 删除数据
                delResColumn = request.POST.get("delResColumn")
                delResValue = request.POST.get("delResValue")
                sqlYueShu = "data_owner_user_account='" + userAccount + "' and " + delResColumn + "='" + delResValue + "'"
                sqlDelData = sqlDeleteData(tableName, sqlYueShu, dataBaseSql['default'])
                bakString = json.dumps([{"deleteLine": sqlDelData}])
            elif sqlType == "modify":  # 修改数据
                columnName = request.POST.get("column")
                columnName = columnName + ", update_user_account, update_time"
                colNameMatrix = stringToColNameMatrix(columnName)  # 获得字段矩阵
                updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 生成数据的更新时间
                dataUpdate = request.POST.get("saveData")
                newSaveData = stringToColNameMatrix(dataUpdate)  # 将string数据转换为矩阵，依据分隔符','
                newSaveData.append(userAccount)
                newSaveData.append(updateTime)
                print(colNameMatrix)
                print(newSaveData)
                restraint = request.POST.get("restraint")
                updateSqlData = sqlUpdateDataLine(tableName, colNameMatrix, newSaveData, restraint, dataBaseSql['default'])
                bakString = json.dumps([{"modify": updateSqlData}])
    print('********dataToDatabaseCAD*dataTransferFyb*fyb********')
    print(bakString)
    return HttpResponse(bakString)


def dataTransferFybAdmin(request):  # 管理员查询数据时不需要校验用户名和企业
    if request.method == "POST":  # ‘POST’方法传递数据
        sqlType = request.POST.get("sqlType")
        userAccount = request.POST.get("userAccount")  # 用户唯一ID
        enterAccount = request.POST.get("enterAccount")  # 企业唯一ID
        tableName = request.POST.get("table")  # 操作表格的名称
        queTableBool = exceptTableJudge('default', tableName)
        #queTableBool = False
        if userAccount != "" and enterAccount != "" and queTableBool == False:  # 用户ID和企业ID不能为空，后期验证安全性，可以在这里添加
            if sqlType == "query":  # 查询数据库
                column = request.POST.get("column")
                columnMatrix = stringToColNameMatrix(column)  # 将’name, phoneid, shunxu‘格式转化为List['name', 'phoneid', 'shunxu']
                restraint = request.POST.get("restraint")
                testDataTemp = []  # 新建矩阵，用来保存返回的数据
                sqlFetchData = sqlSelectData(tableName, columnMatrix, restraint, dataBaseSql['default'])
                #print(sqlFetchData)
                for i in range(len(sqlFetchData)):  # 遍历取回的数据
                    sqlDataDict = {}  # 新建字典
                    for j in range(len(columnMatrix)):  # 按照列名称，将数据保存到字典中
                        sqlDataDict[columnMatrix[j]] = sqlFetchData[i][j]
                    testDataTemp.append(sqlDataDict)
                if len(sqlFetchData) == 0:  # 如果是空数据
                    sqlDataDict = {}  # 新建字典
                    for j in range(len(columnMatrix)):  # 按照列名称，将数据保存到字典中
                        sqlDataDict[columnMatrix[j]] = ''
                    testDataTemp.append(sqlDataDict)  # testDataTemp=[{"name": 'abc',"result": 'testBack',},{"name": 'abcdd',"result": 'rtg',}]
                bakString = json.dumps(testDataTemp)
            elif sqlType == "insert":  # 保存数据到数据库
                columnName = request.POST.get("column")
                columnName = columnName + ", data_owner_user_account, creat_user_account, enter_account, creat_time"
                columnNameMatrix = stringToColNameMatrix(columnName)  # 把列的字符串转化为矩阵
                dataInsert = request.POST.get("saveData")
                createTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 生成数据的创建时间
                print(createTime)
                dataInsertMatrix = stringToColNameMatrix(dataInsert)
                dataInsertMatrix.append(userAccount)
                dataInsertMatrix.append(userAccount)
                dataInsertMatrix.append(enterAccount)
                dataInsertMatrix.append(createTime)
                sqlInsertData = sqlInsertDataLine(tableName, columnNameMatrix, dataInsertMatrix, dataBaseSql['default'])
                bakString = json.dumps([{"insert": sqlInsertData}])
            elif sqlType == "delete":  # 删除数据
                delResColumn = request.POST.get("delResColumn")
                delResValue = request.POST.get("delResValue")
                sqlYueShu = delResColumn + "='" + delResValue + "'"
                sqlDelData = sqlDeleteData(tableName, sqlYueShu, dataBaseSql['default'])
                bakString = json.dumps([{"deleteLine": sqlDelData}])
            elif sqlType == "modify":  # 修改数据
                columnName = request.POST.get("column")
                columnName = columnName + ", update_user_account, update_time"
                colNameMatrix = stringToColNameMatrix(columnName)  # 获得字段矩阵
                updateTime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))  # 生成数据的更新时间
                dataUpdate = request.POST.get("saveData")
                newSaveData = stringToColNameMatrix(dataUpdate)  # 将string数据转换为矩阵，依据分隔符','
                newSaveData.append(userAccount)
                newSaveData.append(updateTime)
                print(colNameMatrix)
                print(newSaveData)
                restraint = request.POST.get("restraint")
                updateSqlData = sqlUpdateDataLine(tableName, colNameMatrix, newSaveData, restraint,
                                                  dataBaseSql['default'])
                bakString = json.dumps([{"modify": updateSqlData}])
    print('********dataToDatabaseCAD*dataTransferFybAdmin*fyb********')
    return HttpResponse(bakString)


# 判断是否是例外的函数，如果是例外的表格，那么返回True
def exceptTableJudge(excDataBase, excTableName):
    bakBool = False
    tempLen = len(exceptTable[excDataBase])
    for i in range(tempLen):
        if exceptTable[excDataBase][i] == excTableName:
            bakBool = True
            break
    return bakBool
